/**
 * 
 */
package org.gov.vis.data.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.gov.vis.data.dto.AIDto;
import org.gov.vis.util.DBConstants;
import org.gov.vis.util.DBStatements;
import org.gov.vis.util.DU;
import org.gov.vis.util.Util;

/**
 * @author Vishwanath
 *
 */
public class AIDaoImpl implements AIDao{

	/**
	 *Method called to either Add or Edit a AI
	 */
	@Override
	public void addOrEditAI(final AIDto _aiDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;

		try {
			conn = Util.getConnection();
			if(_aiDto.getAiId() > 0){
				pStmt = conn.prepareStatement(DBStatements.EDIT_AI);
				pStmt.setString(1, _aiDto.getCode());
				pStmt.setString(2, DU.format(_aiDto.getDoi(), DBConstants.MYSQL_DATE_FORMAT));
				//pStmt.setString(3, _aiDto.getToi());
				pStmt.setString(3, _aiDto.getToi().getDisplayTime());
				pStmt.setString(4, _aiDto.getBreed());
				pStmt.setString(5, _aiDto.getBullNo());
				if(_aiDto.getNoOfAI()!= null && !_aiDto.getNoOfAI().equals("")){
					pStmt.setInt(6, Integer.valueOf(_aiDto.getNoOfAI()));
				}else{
					pStmt.setString(6, null);	
				}
				if(_aiDto.getFee()!= null && !_aiDto.getFee().equals("")){
					pStmt.setDouble(7, Double.parseDouble(_aiDto.getFee()));
				}else{
					pStmt.setString(7, null);
				}
				pStmt.setString(8, _aiDto.getReceiptNo());
				pStmt.setString(9, _aiDto.getCattleOwner());
				pStmt.setString(10, _aiDto.getVillage());

				pStmt.setString(11, _aiDto.getMandal());
				pStmt.setString(12, _aiDto.getAnimalType());
				pStmt.setString(13, _aiDto.getKindOfAnimal());
				if(_aiDto.getBirthGiven()!= null && !_aiDto.getBirthGiven().equals("")){
					pStmt.setInt(14, Integer.valueOf(_aiDto.getBirthGiven()));
				}else{
					pStmt.setString(14, null);
				}				

				pStmt.setLong(15, _aiDto.getAiId());
			}
			else{
				pStmt = conn.prepareStatement(DBStatements.ADD_AI);
				pStmt.setString(1, _aiDto.getCode());
				pStmt.setString(2, DU.format(_aiDto.getDoi(), DBConstants.MYSQL_DATE_FORMAT));
				pStmt.setString(3, _aiDto.getToi().getDisplayTime());
				pStmt.setString(4, _aiDto.getBreed());
				pStmt.setString(5, _aiDto.getBullNo());
				if(_aiDto.getNoOfAI()!= null && !_aiDto.getNoOfAI().equals("")){
					pStmt.setInt(6, Integer.valueOf(_aiDto.getNoOfAI()));
				}else{
					pStmt.setString(6, null);	
				}
				if(_aiDto.getFee()!= null && !_aiDto.getFee().equals("")){
					pStmt.setDouble(7, Double.parseDouble(_aiDto.getFee()));
				}else{
					pStmt.setString(7, null);
				}
				pStmt.setString(8, _aiDto.getReceiptNo());
				pStmt.setString(9, _aiDto.getCattleOwner());
				pStmt.setString(10, _aiDto.getVillage());

				pStmt.setString(11, _aiDto.getMandal());
				pStmt.setString(12, _aiDto.getAnimalType());
				pStmt.setString(13, _aiDto.getKindOfAnimal());
				if(_aiDto.getBirthGiven()!= null && !_aiDto.getBirthGiven().equals("")){
					pStmt.setInt(14, Integer.valueOf(_aiDto.getBirthGiven()));
				}else{
					pStmt.setString(14, null);
				}
			}

			pStmt.executeUpdate();
		} catch (Exception e) {

			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}
	}//end addOrEditDept

	/**
	 *Method called to Delete a AI
	 */
	@Override
	public void deleteAI(final long _aiId) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;

		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.DELETE_AI);
			pStmt.setLong(1, _aiId);
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

	}//end deleteDept

	/**
	 *Method called to get the list of AIs based on the passed AI 
	 */
	@Override
	public List<AIDto> getAIs(final AIDto _aiDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<AIDto> _aiList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_AI);
			if(_aiDto.getAiId() > 0){
				pStmt.setLong(1, _aiDto.getAiId());
				pStmt.setLong(2, _aiDto.getAiId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}

			if(_aiDto.getCode() != null && !_aiDto.getCode().trim().equals("")){
				pStmt.setString(3, _aiDto.getCode());
			}
			else{
				pStmt.setString(3, "%");
			}

			if(_aiDto.getBreed() != null && !_aiDto.getBreed().trim().equals("")){
				pStmt.setString(4, _aiDto.getBreed());
			}
			else{
				pStmt.setString(4, "%");
			}

			if(_aiDto.getBullNo() != null && !_aiDto.getBullNo().trim().equals("")){
				pStmt.setString(5, _aiDto.getBullNo());
			}
			else{
				pStmt.setString(5, "%");
			}

			rsSet = pStmt.executeQuery();

			_aiList = populateAIs(rsSet);
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _aiList;
	}//end getAIs


	/**
	 * Method called to populate the AI records based on the passed result set
	 * @param _rsSet
	 * @return
	 */
	private List<AIDto> populateAIs(final ResultSet _rsSet)throws Exception{
		List<AIDto> _aiList = null;
		AIDto _aiDto = null;

		while(_rsSet.next()){
			if(_rsSet.isFirst()){
				_aiList = new ArrayList<AIDto>();
			}

			//initialize the AI
			_aiDto = new AIDto();
			_aiDto.setAiId(_rsSet.getLong(DBConstants.COL_AI_ID));
			_aiDto.setCode(_rsSet.getString(DBConstants.COL_AI_CODE));
			_aiDto.setDoi(_rsSet.getDate(DBConstants.COL_AI_DOI));
			_aiDto.getToi().setTime(_rsSet.getString(DBConstants.COL_AI_TOI));
			_aiDto.setBreed(_rsSet.getString(DBConstants.COL_AI_BREED));
			_aiDto.setBullNo(_rsSet.getString(DBConstants.COL_AI_BULLNO));
			_aiDto.setNoOfAI(_rsSet.getString(DBConstants.COL_AI_NOAI));
			_aiDto.setFee(_rsSet.getString(DBConstants.COL_AI_FEE));
			_aiDto.setReceiptNo(_rsSet.getString(DBConstants.COL_AI_RECEIPT));
			_aiDto.setCattleOwner(_rsSet.getString(DBConstants.COL_AI_CATTLE_OWNER));			
			_aiDto.setVillage(_rsSet.getString(DBConstants.COL_AI_VILLAGE));
			_aiDto.setMandal(_rsSet.getString(DBConstants.COL_AI_MANDAL));
			_aiDto.setAnimalType(_rsSet.getString(DBConstants.COL_AI_ANIMAL_TYPE));
			_aiDto.setKindOfAnimal(_rsSet.getString(DBConstants.COL_AI_ANIMAL_KIND));
			_aiDto.setBirthGiven(_rsSet.getString(DBConstants.COL_AI_BIRTHS_GIVEN));

			//add the current Object to the list
			_aiList.add(_aiDto);			
		}

		return _aiList;
	}//end populateAIs

	/**
	 * Method is called to search the AIs based on the gopalamithra.
	 */
	@Override
	public List<AIDto> searchAIs(final AIDto _aiDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<AIDto> _aiList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_AI);
			if(_aiDto.getAiId() > 0){
				pStmt.setLong(1, _aiDto.getAiId());
				pStmt.setLong(2, _aiDto.getAiId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}

			if(_aiDto.getCode() != null && !_aiDto.getCode().trim().equals("")){
				pStmt.setString(3,  _aiDto.getCode() + "%");
			}
			else{
				pStmt.setString(3, "%");
			}

			if(_aiDto.getBreed() != null && !_aiDto.getBreed().trim().equals("")){
				pStmt.setString(4, _aiDto.getBreed() + "%");
			}
			else{
				pStmt.setString(4, "%");
			}

			if(_aiDto.getBullNo() != null && !_aiDto.getBullNo().trim().equals("")){
				pStmt.setString(5, _aiDto.getBullNo() + "%");
			}
			else{
				pStmt.setString(5, "%");
			}

			rsSet = pStmt.executeQuery();

			_aiList = populateAIs(rsSet);
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _aiList;
	}//end searchAIs

}//end class
